package com.xhh.nphm.system.mapper;

import com.xhh.nphm.system.domain.AfficheComment;
import com.xhh.nphm.system.domain.Saffiche;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
* @author Administrator
* @description 针对表【sAffiche】的数据库操作Mapper
* @createDate 2022-06-24 14:36:49
* @Entity com.xhh.nphm.system.domain.Saffiche
*/
@Mapper
public interface SafficheDao extends BaseMapper<Saffiche> {
    /**
     * 查询系统公告管理列表数据
     *
     * @param saffiche 封装查询条件
     * @return 列表数据
     */
//    @Select("select AfficheID as myPkVal,NtcTitle,SortName,FromDept,IssueUser,IssueFlag as fbFlag,convert(varchar(19),IssueTime,20) as fbTime," +
//            "Remark from sAffiche where IsDel=0 and userNo=#{userNo}  [[and SortCode=#{sortCode}]] [[and SortName=#{sortName}]] [[and NtcTitle =#{ntcTitle}]] " +
//            "[[and ntcContent =#{ntcContent}]] [[and FromDept =#{fromDept}]] [[and IssueUser =#{issueUser}]]")
    @Select("select AfficheID as myPkVal,NtcTitle,SortName,FromDept,IssueUser,IssueFlag as fbFlag,convert(varchar(19),IssueTime,20) as fbTime," +
            "Remark from sAffiche where IsDel=0 [[and SortCode=#{sortCode}]] [[and SortName=#{sortName}]] [[and NtcTitle =#{ntcTitle}]] " +
            "[[and ntcContent =#{ntcContent}]] [[and FromDept =#{fromDept}]] [[and IssueUser =#{issueUser}]]")
    List<Map<String,Object>> afficheListJson(Saffiche saffiche);


    /**
     * 根据用户查询系统公告列表数据
     *
     * @param saffiche 封装查询条件
     * @return 列表数据
     */
    @Select("select AfficheID as myPkVal,NtcTitle,SortName,FromDept,IssueUser,IssueFlag as fbFlag,convert(varchar(19),IssueTime,20) as fbTime," +
            "Remark from sAffiche where IsDel=0 [[and userNo = #{userNo}]] [[and SortCode=#{sortCode}]] [[and SortName=#{sortName}]] [[and NtcTitle =#{ntcTitle}]] " +
            "[[and ntcContent =#{ntcContent}]] [[and FromDept =#{fromDept}]] [[and IssueUser =#{issueUser}]]")
    List<Map<String,Object>> byUserNoFindAffiche(Saffiche saffiche);


    /**
     * 查询系统通知公告列表数据
     *
     * @param role 用户角色
     * @param saffiche 封装查询条件
     * @return 列表数据
     */
//    @Select("select AfficheID as myPkVal,NtcTitle,SortName,FromDept,IssueUser,convert(varchar(19),IssueTime,20) as fbTime " +
//            "from sAffiche where (ObjRole='-1' or CHARINDEX(''+#{role}+'', ObjRole)>0) and IssueFlag=1 and IsDel=0 and userNo=#{saf.userNo} [[and SortCode=#{saf.sortCode}]] [[and SortName=#{saf.sortName}]]" +
//            "[[and NtcTitle =#{saf.ntcTitle}]] [[and ntcContent =#{saf.ntcContent}]] [[and FromDept =#{saf.fromDept}]] [[and IssueUser =#{saf.issueUser}]]")
    @Select("select AfficheID as myPkVal,NtcTitle,SortName,FromDept,IssueUser,convert(varchar(19),IssueTime,20) as fbTime " +
            "from sAffiche where (ObjRole='-1' or CHARINDEX(''+#{role}+'', ObjRole)>0) and IssueFlag=1 and IsDel=0 [[and SortCode=#{saf.sortCode}]] [[and SortName=#{saf.sortName}]]" +
            "[[and NtcTitle =#{saf.ntcTitle}]] [[and ntcContent =#{saf.ntcContent}]] [[and FromDept =#{saf.fromDept}]] [[and IssueUser =#{saf.issueUser}]]")
    List<Map<String, Object>> ntcListJson(@Param("role") String role, @Param("saf") Saffiche saffiche);

    /**
     * @description 公告
     * @author iutao [[ ]]动态sql查询
     * @return List
     * */
    @Select("select AfficheID,NtcTitle,SortCode,SortName,FromDept,IssueUser,IssueTime,nlogid from sAffiche a" +
            " LEFT JOIN tm_sys_notice_log b  ON a.AfficheID = b.affId and isDelFlag=0  and b.UserId=#{userId} " +
            "where  a.isDel = 0 and a.SortCode in(11,21,12) and b.affId is null" +
            "[[and CHARINDEX(#{content},concat(NtcTitle,NtcContent,FromDept,IssueUser))>0]] order by IssueTime desc")
    List<Saffiche> getNotice(@Param("roleId")String roleId,@Param("content")String content,@Param("userId")Long userId);
    //dbo.contain_one_str(#{roleId},a.ObjRole) > 0 and

    //根据账号部门查询公告
    //本人发的，本级部门和下级部门发的
    @Select("<script> " +
            "select AfficheID,NtcTitle,SortCode,SortName,FromDept,IssueUser,IssueTime,nlogid from sAffiche a " +
            "LEFT JOIN tm_sys_notice_log b ON a.AfficheID = b.affId and isDelFlag=0  and b.UserId=#{userId} " +
            "where a.isDel = 0 and a.SortCode in(11,21,12) and b.affId is null and a.FromDept in " +
            "<foreach item='dept' index='index' collection='list' open='(' separator=',' close=')'> " +
            "   #{dept} " +
            "</foreach>" +
            " <if test='content != null'>" +
            " and CHARINDEX(#{content},concat(NtcTitle,NtcContent,FromDept,IssueUser))>0 " +
            "</if>" +
            " order by IssueTime desc" +
            "</script> ")
    List<Saffiche> byDeptNameFindAffiche(@Param("list") List<String> deptLists,@Param("userId")Long userId,@Param("content")String content);


    /**
     * @Description: 查询通知全部部门的公告
     * @Param: [userId, content]
     * @return: java.util.List<com.xhh.nphm.system.domain.Saffiche>
     */
    @Select("select AfficheID,NtcTitle,SortCode,SortName,FromDept,IssueUser,IssueTime,nlogid from sAffiche a" +
            " LEFT JOIN tm_sys_notice_log b  ON a.AfficheID = b.affId and isDelFlag=0  and b.UserId=#{userId} " +
            "where  a.isDel = 0 and a.SortCode in(11,21,12) and a.objDept='-1' and b.affId is null" +
            "[[and CHARINDEX(#{content},concat(NtcTitle,NtcContent,FromDept,IssueUser))>0]] order by IssueTime desc" )
    List<Saffiche> queryFullDeptAffiche(@Param("userId")Long userId,@Param("content")String content);


    /**
     * @Description: 紧急公告 sortCode = 21
     * @Param: [roleId, content, userId]
     * @return: List
     */
    @Select("select AfficheID,NtcTitle,SortCode,SortName,FromDept,IssueUser,IssueTime,nlogid from sAffiche a" +
            " LEFT JOIN tm_sys_notice_log b ON a.AfficheID = b.affId and isDelFlag=0  and b.UserId=#{userId} " +
            "where  a.isDel = 0 and a.SortCode = 21 and b.affId is null order by IssueTime desc")
    List<Saffiche> getUrgentAffiche(@Param("roleId")String roleId,@Param("userId")Long userId);

    /**
     * @Description: 重要公告 sortCode = 12
     * @Param: [roleId, content, userId]
     * @return: List
     */
    @Select("select AfficheID,NtcTitle,SortCode,SortName,FromDept,IssueUser,IssueTime,nlogid from sAffiche a" +
            " LEFT JOIN tm_sys_notice_log b  ON a.AfficheID = b.affId and isDelFlag=0  and b.UserId=#{userId} " +
            "where  a.isDel = 0 and a.SortCode = 12 and b.affId is null order by IssueTime desc")
    List<Saffiche> getImportantAffiche(@Param("roleId")String roleId,@Param("userId")Long userId);


    /**
     * @Description: 普通公告：sortCode = 11
     * @Param: [roleId, content, userId]
     * @return: List
     */
    @Select("select AfficheID,NtcTitle,SortCode,SortName,FromDept,IssueUser,IssueTime,nlogid from sAffiche a" +
            " LEFT JOIN tm_sys_notice_log b  ON a.AfficheID = b.affId and isDelFlag=0  and b.UserId=#{userId} " +
            "where  a.isDel = 0 and a.SortCode = 11 and b.affId is null order by IssueTime desc")
    List<Saffiche> getCommonAffiche(@Param("roleId")String roleId,@Param("userId")Long userId);

    /**
     * @Description: 已读公告
     * @Param: [roleId, userId]
     * @return: List
     */
    @Select("select AfficheID,NtcTitle,SortCode,SortName,FromDept,IssueUser,IssueTime,nlogid from sAffiche a" +
            " INNER JOIN tm_sys_notice_log b ON a.AfficheID = b.affId and isDelFlag = 0  and b.UserId=#{userId} " +
            "where a.isDel = 0 and a.SortCode in (11,21,12) and a.ReadTimes=1 order by IssueTime desc")
    List<Saffiche> getReadAffiche(@Param("userId")Long userId);

    /**
     * @Description: 查询评论
     * @Param: [id]
     * @return: List
     */
    @Select("select commentId,parentCommentId,afficheId,userId,TrueName as userName,isBlogger,commentContent,editTime,a.isDel from afficheComment a" +
            " Inner Join bUserInfo b on a.userId = b.userNo where afficheId = #{afficheId} and a.isDel = 0 order by editTime desc")
    List<AfficheComment> getAfficheComment(@Param("afficheId") Integer afficheId);

    /**
     * @Description: 修改公告阅读状态
     * @Param: [afficheId]
     * @return: java.lang.Boolean
     */
    @Update("update sAffiche set ReadTimes = 1  where AfficheID = #{afficheId} ;")
    Boolean updateAfficheRead(@Param("afficheId") String afficheId);

    /**
     *
     * @param roles
     * @return
     */
    @Select("select *,row_number() over(partition by SortCode order by IssueTime desc) groupIndex  " +
            "from sAffiche where CHARINDEX(#{roles},ObjRole)>0 " +
            "and IssueFlag = 1 and IsDel = 0 and ReadTimes = 0 and SortCode in (11,21,12) ORDER BY IssueTime desc")
    List<Saffiche> selectSafficheByRoles(String roles);

    /**
     *
     * @param deptIds
     * @return
     */
    @Select("select *,row_number() over(partition by SortCode order by IssueTime desc) groupIndex  " +
            "from sAffiche where CHARINDEX(#{deptIds},ObjDept)>0 " +
            "and IssueFlag = 1 and IsDel = 0 and ReadTimes = 0 and SortCode in (11,21,12) ORDER BY IssueTime desc")
    List<Saffiche> selectSafficheByDeptIds(String deptIds);
}




